﻿Imports AugmentLib.Database
Imports System.Configuration.ConfigurationManager
Imports AugmentLib.WebComponent

Partial Class project
    Inherits System.Web.UI.Page

#Region " Properties "

    Public Property CurrentPage() As Integer
        Get
            'Looks for the current page in ViewState
            Dim objPage As Object = ViewState("_CurrentPage")
            If objPage Is Nothing Then
                Return 0
            Else
                'default to showing the first page
                Return CInt(objPage)
            End If
        End Get
        Set(ByVal value As Integer)
            ViewState("_CurrentPage") = value
        End Set
    End Property

    Public Property PageSize() As Integer
        Get
            'Looks for the current page in ViewState
            Dim objPageSize As Object = ViewState("_PageSize")
            If objPageSize Is Nothing Then
                Return 10
            Else
                'default to showing the first page
                Return CInt(objPageSize)
            End If
        End Get
        Set(ByVal value As Integer)
            ViewState("_PageSize") = value
        End Set
    End Property

#End Region

#Region " Paging and Page Size "

    Protected Sub lnkHead10Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead10Item.Click
        PageSize = 10
         LoadProject()
    End Sub

    Protected Sub lnkHead25Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead25Item.Click
        PageSize = 25
        LoadProject()
    End Sub

    Protected Sub lnkHead100Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead100Item.Click
        PageSize = 100
         LoadProject()
    End Sub

    Protected Sub lnkHead150Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkHead150Item.Click
        PageSize = 150
         LoadProject()
    End Sub

    Protected Sub lnkFoot10Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot10Item.Click
        PageSize = 10
        LoadProject()
    End Sub

    Protected Sub lnkFoot25Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot25Item.Click
        PageSize = 25
         LoadProject()
    End Sub

    Protected Sub lnkFoot100Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot100Item.Click
        PageSize = 100
         LoadProject()
    End Sub

    Protected Sub lnkFoot150Item_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkFoot150Item.Click
        PageSize = 150
         LoadProject()
    End Sub

    Protected Sub btnMoveFirst_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
        CurrentPage = 0
        txtPageNo.Text = CurrentPage + 1
         LoadProject()
    End Sub

    Protected Sub btnPrevious_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
        If CurrentPage > 0 Then
            CurrentPage = CurrentPage - 1
            txtPageNo.Text = CurrentPage + 1
            LoadProject()
        End If
    End Sub

    Protected Sub btnGo_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGo.Click
        If CInt(txtPageNo.Text < 1) Then
            txtPageNo.Text = 1
        End If

        If CInt(txtPageNo.Text > CInt(lblMaxPageCount.Text)) Then
            txtPageNo.Text = CInt(lblMaxPageCount.Text) + 1
        End If

        CurrentPage = CInt(txtPageNo.Text) - 1
         LoadProject()

    End Sub

    Protected Sub btnNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If CurrentPage < CInt(lblMaxPageCount.Text) Then
            CurrentPage = CurrentPage + 1
            txtPageNo.Text = CurrentPage + 1
             LoadProject()
        End If
    End Sub

    Protected Sub btnMoveLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
        CurrentPage = CInt(lblMaxPageCount.Text)
        txtPageNo.Text = CurrentPage + 1
         LoadProject()
    End Sub

#End Region

    Private Sub LoadProject()

        Dim CountryFilter As String = String.Empty
        Dim StateFilter As String = String.Empty
        Dim LocationFilter As String = String.Empty
        Dim RoadFilter As String = String.Empty
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        Dim TempDT As New Data.DataTable

        objDB.OpenConnection()

        'edited by minginn @ 28/6/2011 - to load the listing according to search criteria after edit but not after save
        'to add in filtering for country, state
        'If strCountry <> "" Then
        '    CountryFilter = " AND s.CountryId = '" & strCountry & "'"
        'End If

        'If strState <> "" Then
        '    StateFilter = " AND s.StateId = '" & strState & "'"
        'End If

        'If strLocation <> "" Then
        '    LocationFilter = " AND l.LocationId = '" & strLocation & "'"
        'End If

        'If strRoad <> "" Then
        '    RoadFilter = " AND r.RoadId = '" & strRoad & "'"
        'End If
        If ddlCountryFilter.SelectedValue <> "" Then
            CountryFilter = " AND s.CountryId = '" & ddlCountryFilter.SelectedValue & "'"
        End If

        If ddlStateFilter.SelectedValue <> "" Then
            StateFilter = " AND s.StateId = '" & ddlStateFilter.SelectedValue & "'"
        End If

        If ddlLocationFilter.SelectedValue <> "" Then
            LocationFilter = " AND l.LocationId = '" & ddlLocationFilter.SelectedValue & "'"
        End If

        If ddlRoadFilter.SelectedValue <> "" Then
            RoadFilter = " AND r.RoadId = '" & ddlRoadFilter.SelectedValue & "'"
        End If

        If txtSearch.Text = "" Then
            objDB.dbVariable.SQLString = "SELECT ProjectId,Project,ProjectDesc,r.RoadId,Road,l.LocationId,Location,l.StateId,s.CountryId,State,Country,p.Picture FROM AW_State s " & _
                                        "INNER JOIN AW_Country c ON s.CountryId = c.CountryId " & _
                                        "INNER JOIN AW_Location l ON l.StateId = s.StateId " & _
                                        "INNER JOIN AW_Road r ON r.LocationId = l.LocationId " & _
                                        "INNER JOIN AW_Project p ON p.RoadId = r.RoadId " & _
                                        "WHERE p.StatusDelete=0" & CountryFilter & StateFilter & LocationFilter & RoadFilter & lblHidSortType.Text
            objDB.FillData(TempDT, "AW_Project", Database.SQLCommandType.NormalString)
        Else
            objDB.dbVariable.SQLString = "SELECT ProjectId,Project,ProjectDesc,r.RoadId,Road,l.LocationId,Location,l.StateId,s.CountryId,State,Country,p.Picture FROM AW_State s " & _
                                        "INNER JOIN AW_Country c ON s.CountryId = c.CountryId " & _
                                        "INNER JOIN AW_Location l ON l.StateId = s.StateId " & _
                                        "INNER JOIN AW_Road r ON r.LocationId = l.LocationId " & _
                                        "INNER JOIN AW_Project p ON p.RoadId = r.RoadId " & _
                                        "WHERE p.StatusDelete=0 AND Project LIKE @Project" & CountryFilter & StateFilter & LocationFilter & RoadFilter & lblHidSortType.Text
            objDB.AddParameter("Project", Data.SqlDbType.NVarChar, "%" & txtSearch.Text.Trim & "%")
            objDB.FillData(TempDT, "AW_Project", Database.SQLCommandType.NormalString)
        End If
        objDB.CloseConnection()

        'Creating a Paged Datasouce
        Dim objPaged As New PagedDataSource
        objPaged.DataSource = TempDT.DefaultView
        objPaged.AllowPaging = True
        objPaged.PageSize = PageSize
        objPaged.CurrentPageIndex = CurrentPage

        lblHeadNoOfItem.Text = TempDT.Rows.Count
        lblFootNoOfItem.Text = TempDT.Rows.Count
        lblMaxPageCount.Text = objPaged.PageCount - 1

        repProject.DataSource = objPaged
        repProject.DataBind()

    End Sub

    Protected Sub imgNewProject_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles imgNewProject.Click
        GetddlCountry()
        GetddlState(ddlCountry.SelectedValue)
        GetddlLocation(ddlState.SelectedValue)
        GetddlRoad(ddlLocation.SelectedValue)
        txtProject.Text = String.Empty
        txtProjectDesc.Text = String.Empty
        ClearAllField()
        mpeEntry.Show()
    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Session("StaffId") Is Nothing Then
            Response.Redirect("Default.aspx")
        ElseIf Session.Item("StaffType").ToString = "2" Then
            Response.Redirect("Default.aspx")
        Else
            lblStaffUID.Text = Session("StaffId").ToString
        End If

        If Not Page.IsPostBack Then
            GetddlCountryFilter()
            GetddlStateFilter(ddlCountryFilter.SelectedValue)
            GetddlLocationFilter(ddlStateFilter.SelectedValue)
            GetddlRoadFilter(ddlLocationFilter.SelectedValue)
            LoadProject()
            'Added by : Daniel Lee
            'Date : 22-Apr-2011
            'Purpose : To auto update each other when typing the search criteria
            txtSearch.Attributes.Add("onKeyUp", "SearchReplace('" & txtSearch.ClientID & "','" & txtFilterSearch.ClientID & "')")
            txtSearch.Attributes.Add("onPaste", "SearchReplace('" & txtSearch.ClientID & "','" & txtFilterSearch.ClientID & "')")
            txtFilterSearch.Attributes.Add("onKeyUp", "SearchReplace('" & txtFilterSearch.ClientID & "','" & txtSearch.ClientID & "')")
            txtFilterSearch.Attributes.Add("onPaste", "SearchReplace('" & txtFilterSearch.ClientID & "','" & txtSearch.ClientID & "')")
        End If
    End Sub

    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        ddlCountryFilter.SelectedIndex = 0
        ddlStateFilter.SelectedIndex = 0
        ddlLocationFilter.SelectedIndex = 0
        ddlRoadFilter.SelectedIndex = 0
        'txtFilterSearch.Text = txtSearch.Text.Trim
        LoadProject()
    End Sub

    Protected Sub repProject_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.RepeaterCommandEventArgs) Handles repProject.ItemCommand
        If e.CommandName.ToUpper = "EDITITEM" Then
            GetddlCountry()
            ddlCountry.SelectedValue = CType(repProject.Items(e.CommandArgument).FindControl("lblRepCountryUID"), Label).Text
            GetddlState(ddlCountry.SelectedValue)
            ddlState.SelectedValue = CType(repProject.Items(e.CommandArgument).FindControl("lblRepStateUID"), Label).Text
            GetddlLocation(ddlState.SelectedValue)
            ddlLocation.SelectedValue = CType(repProject.Items(e.CommandArgument).FindControl("lblRepLocationUID"), Label).Text
            GetddlRoad(ddlLocation.SelectedValue)
            ddlRoad.SelectedValue = CType(repProject.Items(e.CommandArgument).FindControl("lblRepRoadUID"), Label).Text
            txtProject.Text = CType(repProject.Items(e.CommandArgument).FindControl("lblRepProject"), Label).Text
            txtProjectDesc.Text = CType(repProject.Items(e.CommandArgument).FindControl("lblRepProjectDesc"), Label).Text
            lblProjectUID.Text = CType(repProject.Items(e.CommandArgument).FindControl("lblRepProjectUID"), Label).Text
            lblOldPicture.Text = CType(repProject.Items(e.CommandArgument).FindControl("lblRepPicture"), Label).Text

            If lblOldPicture.Text <> "" Then
                imgProjectPhoto.ImageUrl = "project/" & lblOldPicture.Text
                imgProjectPhoto.Visible = True
            Else

            End If
            mpeEntry.Show()
        Else
            'Begin Delete
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))

            objDB.OpenConnection()
            objDB.dbVariable.SQLString = "UPDATE AW_Project SET StatusDelete=1 WHERE ProjectId=@ProjectId"
            objDB.AddParameter("ProjectId", Data.SqlDbType.UniqueIdentifier, New Guid(CType(repProject.Items(e.CommandArgument).FindControl("lblRepProjectUID"), Label).Text))
            objDB.ExecuteQuery()
            objDB.CloseConnection()
            lblMsgSuccessful.Text = "Project Deleted"
            lblMsgSuccessful.Visible = True
             LoadProject()
        End If
    End Sub

    Private Sub ClearAllField()
        txtProject.Text = String.Empty
        ddlRoad.SelectedIndex = 0
        ddlLocation.SelectedIndex = 0
        ddlCountry.SelectedIndex = 0
        ddlState.SelectedIndex = 0
        lblProjectUID.Text = String.Empty
        divError.Visible = False
        divSuccessful.Visible = False
    End Sub

    Private Sub ClearFilterField()
        txtFilterSearch.Text = String.Empty
        txtSearch.Text = String.Empty
        ddlCountryFilter.SelectedIndex = 0
        ddlStateFilter.SelectedIndex = 0
        ddlLocationFilter.SelectedIndex = 0
        ddlRoadFilter.SelectedIndex = 0
    End Sub

    Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click

        If lblProjectUID.Text = "" Then
            'New
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))


            Dim ExistProject As String
            ExistProject = ProjectExist(objDB)
            If ExistProject = "N" Then
                ClearAllField()
                ClearFilterField()
                divError.Visible = True
                lblMsgError.Text = "Location Already Exist"
                LoadProject()
            ElseIf ExistProject <> "" Then
                Dim ProjectPhotoImg As HttpPostedFile
                Dim UploadedProjectPhotoImg As String = ""
                Dim UploadProjectPhotoError As String = ""

                Try
                    If filProjectPicture.HasFile Then
                        ProjectPhotoImg = filProjectPicture.PostedFile
                        If System.IO.Path.GetExtension(ProjectPhotoImg.FileName).ToLower() <> ".jpg" And System.IO.Path.GetExtension(ProjectPhotoImg.FileName).ToLower() <> ".png" And System.IO.Path.GetExtension(ProjectPhotoImg.FileName).ToLower() <> ".png" Then
                            UploadProjectPhotoError = UploadProjectPhotoError & ProjectPhotoImg.FileName & " can only be JPG,GIF and PNG format<br />"
                        Else
                            Dim objImgComp As New ImageComp
                            UploadedProjectPhotoImg = objImgComp.UploadSingleImage(ProjectPhotoImg, "project", False, 150)
                        End If
                    End If
                Catch ex As Exception
                    UploadProjectPhotoError = UploadProjectPhotoError & filProjectPicture.PostedFile.FileName & " has error with error message : " & ex.Message & "<br />"
                End Try

                Try
                    If UploadProjectPhotoError = "" Then
                        If UploadedProjectPhotoImg = "" Then
                            objDB.dbVariable.SQLString = "UPDATE AW_Project SET StatusDelete=@StatusDelete WHERE ProjectId=@ProjectId"
                        Else
                            objDB.dbVariable.SQLString = "UPDATE AW_Project SET StatusDelete=@StatusDelete, Picture=@Picture WHERE ProjectId=@ProjectId"
                            objDB.AddParameter("Picture", Data.SqlDbType.NVarChar, UploadedProjectPhotoImg)
                        End If
                    Else
                        lblMsgError.Text = UploadProjectPhotoError
                        divError.Visible = True
                        Exit Sub
                    End If
                    objDB.AddParameter("ProjectId", Data.SqlDbType.UniqueIdentifier, New Guid(ExistProject))
                    objDB.AddParameter("StatusDelete", Data.SqlDbType.TinyInt, 0)
                    objDB.OpenConnection()
                    objDB.ExecuteQuery()
                    objDB.CloseConnection()
                    If lblOldPicture.Text <> "" Then
                        'Remove Old Picture
                        If UploadedProjectPhotoImg <> "" Then
                            Dim objImgComp As New ImageComp
                            objImgComp.DeleteSingleFile("project", lblOldPicture.Text)
                        End If
                    End If
                    ClearAllField()
                    ClearFilterField()
                    divSuccessful.Visible = True
                    lblMsgSuccessful.Text = "Project Successfully Updated"
                    LoadProject()
                Catch ex As Exception
                    If objDB.dbVariable.objConn.State = Data.ConnectionState.Open Then
                        objDB.CloseConnection()
                    End If
                    lblMsgError.Text = ex.Message
                    divError.Visible = True
                    If UploadedProjectPhotoImg <> "" Then
                        Dim objImgComp As New ImageComp
                        objImgComp.DeleteSingleFile("project", UploadedProjectPhotoImg)
                    End If
                End Try

            Else
                    Dim ProjectPhotoImg As HttpPostedFile
                    Dim UploadedProjectPhotoImg As String = ""
                    Dim UploadProjectPhotoError As String = ""

                    Try
                        If filProjectPicture.HasFile Then
                            ProjectPhotoImg = filProjectPicture.PostedFile
                            If System.IO.Path.GetExtension(ProjectPhotoImg.FileName).ToLower() <> ".jpg" And System.IO.Path.GetExtension(ProjectPhotoImg.FileName).ToLower() <> ".png" And System.IO.Path.GetExtension(ProjectPhotoImg.FileName).ToLower() <> ".png" Then
                                UploadProjectPhotoError = UploadProjectPhotoError & ProjectPhotoImg.FileName & " can only be JPG,GIF and PNG format<br />"
                            Else
                                Dim objImgComp As New ImageComp
                                UploadedProjectPhotoImg = objImgComp.UploadSingleImage(ProjectPhotoImg, "project", False, 150)
                            End If
                        End If
                    Catch ex As Exception
                        UploadProjectPhotoError = UploadProjectPhotoError & filProjectPicture.PostedFile.FileName & " has error with error message : " & ex.Message & "<br />"
                    End Try
                Try
                    If UploadProjectPhotoError = "" Then
                        If UploadedProjectPhotoImg = "" Then
                            objDB.dbVariable.SQLString = "INSERT INTO AW_Project (ProjectId,RoadId,Project,ProjectDesc,CreateDate,StatusDelete) VALUES (@ProjectId,@RoadId,@Project,@ProjectDesc,@CreateDate,@StatusDelete)"
                        Else
                            objDB.dbVariable.SQLString = "INSERT INTO AW_Project (ProjectId,RoadId,Project,ProjectDesc,CreateDate,StatusDelete,Picture) VALUES (@ProjectId,@RoadId,@Project,@ProjectDesc,@CreateDate,@StatusDelete,@Picture)"
                            objDB.AddParameter("Picture", Data.SqlDbType.NVarChar, UploadedProjectPhotoImg)
                        End If
                    Else
                        lblMsgError.Text = UploadProjectPhotoError
                        divError.Visible = True
                        Exit Sub
                    End If


                    objDB.AddParameter("ProjectId", Data.SqlDbType.UniqueIdentifier, Guid.NewGuid)
                    objDB.AddParameter("RoadId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlRoad.SelectedValue))
                    objDB.AddParameter("Project", Data.SqlDbType.NVarChar, txtProject.Text.Trim.ToUpper)
                    objDB.AddParameter("ProjectDesc", Data.SqlDbType.NVarChar, txtProjectDesc.Text.Trim.ToUpper)
                    objDB.AddParameter("CreateDate", Data.SqlDbType.DateTime, Now)
                    objDB.AddParameter("StatusDelete", Data.SqlDbType.TinyInt, 0)
                    objDB.OpenConnection()
                    objDB.ExecuteQuery()
                    objDB.CloseConnection()
                    ClearAllField()
                    ClearFilterField()
                    divSuccessful.Visible = True
                    lblMsgSuccessful.Text = "Project Successfully Inserted"
                    LoadProject()
                Catch ex As Exception
                    If objDB.dbVariable.objConn.State = Data.ConnectionState.Open Then
                        objDB.CloseConnection()
                    End If
                    lblMsgError.Text = ex.Message
                    divError.Visible = True
                    If UploadedProjectPhotoImg <> "" Then
                        Dim objImgComp As New ImageComp
                        objImgComp.DeleteSingleFile("project", UploadedProjectPhotoImg)
                    End If
                End Try
            End If



        Else
            'Edit
            Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
            Dim ProjectPhotoImg As HttpPostedFile
            Dim UploadedProjectPhotoImg As String = ""
            Dim UploadProjectPhotoError As String = ""

            Try
                If filProjectPicture.HasFile Then
                    ProjectPhotoImg = filProjectPicture.PostedFile
                    If System.IO.Path.GetExtension(ProjectPhotoImg.FileName).ToLower() <> ".jpg" And System.IO.Path.GetExtension(ProjectPhotoImg.FileName).ToLower() <> ".png" And System.IO.Path.GetExtension(ProjectPhotoImg.FileName).ToLower() <> ".png" Then
                        UploadProjectPhotoError = UploadProjectPhotoError & ProjectPhotoImg.FileName & " can only be JPG,GIF and PNG format<br />"
                    Else
                        Dim objImgComp As New ImageComp
                        UploadedProjectPhotoImg = objImgComp.UploadSingleImage(ProjectPhotoImg, "project", False, 150)
                    End If
                End If
            Catch ex As Exception
                UploadProjectPhotoError = UploadProjectPhotoError & filProjectPicture.PostedFile.FileName & " has error with error message : " & ex.Message & "<br />"
            End Try

            Try
                If UploadProjectPhotoError = "" Then
                    If UploadedProjectPhotoImg = "" Then
                        objDB.dbVariable.SQLString = "UPDATE AW_Project SET Project=@Project,ProjectDesc=@ProjectDesc, RoadId=@RoadId WHERE ProjectId=@ProjectId"
                    Else
                        objDB.dbVariable.SQLString = "UPDATE AW_Project SET Project=@Project,ProjectDesc=@ProjectDesc, RoadId=@RoadId,Picture=@Picture WHERE ProjectId=@ProjectId"
                        objDB.AddParameter("Picture", Data.SqlDbType.NVarChar, UploadedProjectPhotoImg)
                    End If
                Else
                    lblMsgError.Text = UploadProjectPhotoError
                    divError.Visible = True
                    Exit Sub
                End If

                objDB.OpenConnection()
                'objDB.dbVariable.SQLString = "UPDATE AW_Project SET Project=@Project,ProjectDesc=@ProjectDesc, RoadId=@RoadId WHERE ProjectId=@ProjectId"
                objDB.AddParameter("ProjectId", Data.SqlDbType.UniqueIdentifier, New Guid(lblProjectUID.Text))
                objDB.AddParameter("RoadId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlRoad.SelectedValue))
                objDB.AddParameter("Project", Data.SqlDbType.NVarChar, txtProject.Text.Trim.ToUpper)
                objDB.AddParameter("ProjectDesc", Data.SqlDbType.NVarChar, txtProjectDesc.Text.Trim.ToUpper)
                objDB.ExecuteQuery()
                objDB.CloseConnection()
                ClearAllField()
                divSuccessful.Visible = True
                lblMsgSuccessful.Text = "Project Successfully Updated"
                LoadProject()
            Catch ex As Exception
                If objDB.dbVariable.objConn.State = Data.ConnectionState.Open Then
                    objDB.CloseConnection()
                End If
                lblMsgError.Text = ex.Message
                divError.Visible = True
                If UploadedProjectPhotoImg <> "" Then
                    Dim objImgComp As New ImageComp
                    objImgComp.DeleteSingleFile("project", UploadedProjectPhotoImg)
                End If
            End Try
        End If
    End Sub

    Private Function ProjectExist(ByRef objDB As Database) As String
        Dim Tempdt As New Data.DataTable
        Dim strProject As String = String.Empty

        objDB.dbVariable.SQLString = "SELECT Project, ProjectId, StatusDelete FROM AW_Project WHERE RoadId=@RoadId AND Project=@Project"
        objDB.AddParameter("RoadId", Data.SqlDbType.UniqueIdentifier, New Guid(ddlRoad.SelectedValue))
        objDB.AddParameter("Project", Data.SqlDbType.NVarChar, txtProject.Text.Trim.ToUpper)
        objDB.FillData(Tempdt, "AW_Project", Database.SQLCommandType.NormalString)
        objDB.ClearParameter()
        If Tempdt.Rows.Count > 0 Then
            For Each row As Data.DataRow In Tempdt.Rows
                If row.Item("StatusDelete").ToString = "0" Then
                    strProject = "N"
                Else
                    strProject = row.Item("ProjectId").ToString
                End If
            Next
        Else
            strProject = ""
        End If
        Return strProject
    End Function

    Private Sub GetddlCountry()
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT CountryId,Country FROM AW_Country WHERE StatusDelete = 0 ORDER BY Country"
        objDB.FillData(TempDT, "Country", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlCountry
            .DataSource = TempDT
            .DataTextField = "Country"
            .DataValueField = "CountryId"
            .DataBind()
            .Items.Insert(0, "-SELECT-")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()
    End Sub

    Private Sub GetddlState(ByVal strCountry As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strCountry = "" Then
            strCountry = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT StateId,State FROM AW_State WHERE CountryId = @CountryId AND StatusDelete = 0 ORDER BY State"
        objDB.AddParameter("CountryId", Data.SqlDbType.UniqueIdentifier, New Guid(strCountry))
        objDB.FillData(TempDT, "State", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlState
            .DataSource = TempDT
            .DataTextField = "State"
            .DataValueField = "StateId"
            .DataBind()
            .Items.Insert(0, "-SELECT-")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Private Sub GetddlLocation(ByVal strState As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strState = "" Then
            strState = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT LocationId,Location FROM AW_Location WHERE StateId = @StateId AND StatusDelete = 0 ORDER BY Location"
        objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, New Guid(strState))
        objDB.FillData(TempDT, "Location", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlLocation
            .DataSource = TempDT
            .DataTextField = "Location"
            .DataValueField = "LocationId"
            .DataBind()
            .Items.Insert(0, "-SELECT-")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Private Sub GetddlRoad(ByVal strLocation As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strLocation = "" Then
            strLocation = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT RoadId,Road FROM AW_Road WHERE LocationId = @LocationId AND StatusDelete = 0 ORDER BY Road"
        objDB.AddParameter("LocationId", Data.SqlDbType.UniqueIdentifier, New Guid(strLocation))
        objDB.FillData(TempDT, "Road", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlRoad
            .DataSource = TempDT
            .DataTextField = "Road"
            .DataValueField = "RoadId"
            .DataBind()
            .Items.Insert(0, "-SELECT-")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Private Sub GetddlCountryFilter()
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        objDB.dbVariable.SQLString = "SELECT CountryId,Country FROM AW_Country WHERE StatusDelete = 0 ORDER BY Country"
        objDB.FillData(TempDT, "Country", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "ANY"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlCountryFilter
            .DataSource = TempDT
            .DataTextField = "Country"
            .DataValueField = "CountryId"
            .DataBind()
            .Items.Insert(0, "ANY")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub


    Private Sub GetddlStateFilter(ByVal strCountry As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strCountry = "" Then
            strCountry = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT StateId,State FROM AW_State WHERE CountryId = @CountryId AND StatusDelete = 0 ORDER BY State"
        objDB.AddParameter("CountryId", Data.SqlDbType.UniqueIdentifier, New Guid(strCountry))
        objDB.FillData(TempDT, "State", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "ANY"
        'TempDT.Rows.InsertAt(dtrow, 0)      

        With ddlStateFilter
            .DataSource = TempDT
            .DataTextField = "State"
            .DataValueField = "StateId"
            .DataBind()
            .Items.Insert(0, "ANY")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Private Sub GetddlLocationFilter(ByVal strState As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strState = "" Then
            strState = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT LocationId,Location FROM AW_Location WHERE StateId = @StateId AND StatusDelete = 0 ORDER BY Location"
        objDB.AddParameter("StateId", Data.SqlDbType.UniqueIdentifier, New Guid(strState))
        objDB.FillData(TempDT, "Location", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlLocationFilter
            .DataSource = TempDT
            .DataTextField = "Location"
            .DataValueField = "LocationId"
            .DataBind()
            .Items.Insert(0, "ANY")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Private Sub GetddlRoadFilter(ByVal strLocation As String)
        Dim TempDT As New Data.DataTable
        Dim objDB As New Database(AppSettings("datasource"), AppSettings("database"), AppSettings("username"), AppSettings("password"), AppSettings("timeout"), AppSettings("pooling"))
        objDB.OpenConnection()

        If strLocation = "" Then
            strLocation = Guid.Empty.ToString
        End If

        objDB.dbVariable.SQLString = "SELECT RoadId,Road FROM AW_Road WHERE LocationId = @LocationId AND StatusDelete = 0 ORDER BY Road"
        objDB.AddParameter("LocationId", Data.SqlDbType.UniqueIdentifier, New Guid(strLocation))
        objDB.FillData(TempDT, "Road", Database.SQLCommandType.NormalString)

        'Dim dtrow As Data.DataRow
        'dtrow = TempDT.NewRow()
        'dtrow.Item(0) = "00000000-0000-0000-0000-000000000000"
        'dtrow.Item(1) = "-SELECT-"
        'TempDT.Rows.InsertAt(dtrow, 0)

        With ddlRoadFilter
            .DataSource = TempDT
            .DataTextField = "Road"
            .DataValueField = "RoadId"
            .DataBind()
            .Items.Insert(0, "ANY")
            .Items(0).Value = ""
        End With

        objDB.CloseConnection()

    End Sub

    Protected Sub btnSearchFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearchFilter.Click
        txtSearch.Text = txtFilterSearch.Text.Trim
        LoadProject()
    End Sub

    Protected Sub btnResetSearchFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnResetSearchFilter.Click
        txtFilterSearch.Text = String.Empty
        txtSearch.Text = String.Empty
        ddlCountryFilter.SelectedIndex = 0
        ddlStateFilter.SelectedIndex = 0
        ddlLocationFilter.SelectedIndex = 0
        ddlRoadFilter.SelectedIndex = 0
    End Sub

    Protected Sub ddlCountryFilter_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCountryFilter.SelectedIndexChanged
        GetddlStateFilter(ddlCountryFilter.SelectedValue)
        LoadProject()
    End Sub

    Protected Sub ddlStateFilter_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlStateFilter.SelectedIndexChanged
        GetddlLocationFilter(ddlStateFilter.SelectedValue)
        LoadProject()
    End Sub

    Protected Sub ddlLocationFilter_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlLocationFilter.SelectedIndexChanged
        GetddlRoadFilter(ddlLocationFilter.SelectedValue)
        LoadProject()
    End Sub

    Protected Sub ddlRoadFilter_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlRoadFilter.SelectedIndexChanged
        LoadProject()
    End Sub

    Protected Sub ddlCountry_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCountry.SelectedIndexChanged
        GetddlState(ddlCountry.SelectedValue)
        mpeEntry.Show()
    End Sub

    Protected Sub ddlState_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlState.SelectedIndexChanged
        GetddlLocation(ddlState.SelectedValue)
        mpeEntry.Show()
    End Sub
  
    Protected Sub ddlLocation_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlLocation.SelectedIndexChanged
        GetddlRoad(ddlLocation.SelectedValue)
        mpeEntry.Show()
    End Sub

    Private Function SortType(ByVal strSortType As String) As String
        Dim strSort As String
        If Right(strSortType, 1) = "▼" Then
            strSort = "ASC"
        Else
            strSort = "DESC"
        End If
        Return strSort
    End Function

    Protected Sub lnkProject_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkProject.Click
        If lnkProject.Text = "Project▼" Then
            lnkProject.Text = "Project▲"
        Else
            lnkProject.Text = "Project▼"
        End If

        lblHidSortType.Text = " ORDER BY Project " & SortType(lnkProject.Text) & ", Road " & SortType(lnkRoad.Text) & _
                        ", Location " & SortType(lnkLocation.Text) & ", State " & SortType(lnkState.Text) & _
                        ", Country " & SortType(lnkCountry.Text) & ", ProjectDesc " & SortType(lnkProjectDesc.Text)
        LoadProject()
    End Sub

    Protected Sub lnkRoad_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkRoad.Click
        If lnkRoad.Text = "Road▼" Then
            lnkRoad.Text = "Road▲"
        Else
            lnkRoad.Text = "Road▼"
        End If

        lblHidSortType.Text = " ORDER BY Road " & SortType(lnkRoad.Text) & ", Project " & SortType(lnkProject.Text) & _
                        ", Location " & SortType(lnkLocation.Text) & ", State " & SortType(lnkState.Text) & _
                        ", Country " & SortType(lnkCountry.Text) & ", ProjectDesc " & SortType(lnkProjectDesc.Text)
        LoadProject()
    End Sub

    Protected Sub lnkLocation_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkLocation.Click
        If lnkLocation.Text = "Location▼" Then
            lnkLocation.Text = "Location▲"
        Else
            lnkLocation.Text = "Location▼"
        End If

        lblHidSortType.Text = " ORDER BY Location " & SortType(lnkLocation.Text) & ", Project " & SortType(lnkProject.Text) & _
                        ", Road " & SortType(lnkRoad.Text) & ", State " & SortType(lnkState.Text) & _
                        ", Country " & SortType(lnkCountry.Text) & ", ProjectDesc " & SortType(lnkProjectDesc.Text)
        LoadProject()
    End Sub

    Protected Sub lnkState_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkState.Click
        If lnkState.Text = "State▼" Then
            lnkState.Text = "State▲"
        Else
            lnkState.Text = "State▼"
        End If

        lblHidSortType.Text = " ORDER BY State " & SortType(lnkState.Text) & ", Project " & SortType(lnkProject.Text) & _
                        ", Road " & SortType(lnkRoad.Text) & ", Location " & SortType(lnkLocation.Text) & _
                        ", Country " & SortType(lnkCountry.Text) & ", ProjectDesc " & SortType(lnkProjectDesc.Text)
        LoadProject()
    End Sub

    Protected Sub lnkCountry_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkCountry.Click
        If lnkCountry.Text = "Country▼" Then
            lnkCountry.Text = "Country▲"
        Else
            lnkCountry.Text = "Country▼"
        End If

        lblHidSortType.Text = " ORDER BY Country " & SortType(lnkCountry.Text) & ", Project " & SortType(lnkProject.Text) & _
                        ", Road " & SortType(lnkRoad.Text) & ", Location " & SortType(lnkLocation.Text) & _
                        ", State " & SortType(lnkState.Text) & ", ProjectDesc " & SortType(lnkProjectDesc.Text)
        LoadProject()
    End Sub

    Protected Sub lnkProjectDesc_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lnkProjectDesc.Click
        If lnkProjectDesc.Text = "Project Description▼" Then
            lnkProjectDesc.Text = "Project Description▲"
        Else
            lnkProjectDesc.Text = "Project Description▼"
        End If

        lblHidSortType.Text = " ORDER BY ProjectDesc " & SortType(lnkProjectDesc.Text) & ", Project " & SortType(lnkProject.Text) & _
                        ", Road " & SortType(lnkRoad.Text) & ", Location " & SortType(lnkLocation.Text) & _
                        ", State " & SortType(lnkState.Text) & ", Country " & SortType(lnkCountry.Text)
        LoadProject()
    End Sub

    Protected Sub repProject_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles repProject.ItemDataBound
        If e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then

            If CType(e.Item.FindControl("lblRepPicture"), Label).Text <> "" Then
                CType(e.Item.FindControl("imgRepProjectPicture"), Image).ImageUrl = "project/" & CType(e.Item.FindControl("lblRepPicture"), Label).Text
                CType(e.Item.FindControl("imgRepProjectPicture"), Image).Visible = True
            Else
                CType(e.Item.FindControl("imgRepProjectPicture"), Image).Visible = False
            End If

            CType(e.Item.FindControl("cbeDelete"), Object).confirmText = "Are you sure you want to delete " & CType(e.Item.FindControl("lblRepProject"), Label).Text

        End If
    End Sub
End Class
